USE [ConversionDB]
GO
/****** Object:  StoredProcedure [dbo].[SyncDataFromStudentDB]    Script Date: 03/30/2016 17:24:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Taylor Chase
-- Create date: September 9, 2015
-- Description:	Synchronizes the student data from the student database to CoreData on mboa
--				and refreshes the PowerSchool parent email addresses
-- =============================================
ALTER PROCEDURE [dbo].[SyncDataFromStudentDB] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
-- Queries to test validation *************************************************************
-- run them to clear values out of coredata in order to test the insert and update statements.
--PRINT 'Step 1'
--UPDATE [MBOA].[CoreData].[dbo].[Persons] set gradyear = 2021 WHERE personid = '7818';
--PRINT 'Step 2'
--DELETE FROM MBOA.CoreData.dbo.Email_Addresses WHERE PersonID = '4498'
--DELETE FROM MBOA.CoreData.dbo.Person_Relationships WHERE Person2ID = '4498'
--DELETE FROM MBOA.CoreData.dbo.Persons WHERE PersonID = '4498'
--PRINT 'Step 3'
--UPDATE MBOA.CoreData.dbo.Persons SET Fname='Rodney' WHERE PersonID = '11111'
--PRINT 'Step 4'
--DELETE FROM MBOA.CoreData.dbo.Addresses WHERE PersonID = '12083'
--DELETE FROM MBOA.CoreData.dbo.Person_Relationships WHERE Person1ID = '12083'
--DELETE FROM MBOA.CoreData.dbo.Persons WHERE PersonID = '12083'
	
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 01 Existing Students';
UPDATE P
SET P.Lname = [LastName], P.Fname = [FirstName], P.Pname = [PreferedName], P.Mname = [MiddleName], P.Gradyear = [Grad-Year], P.Citizenship1 = [Citizenship], P.Citizenship2 = S.[Citizenship2], P.Nationality1 = [Nationality], P.Gender = [Sex], P.Bdate = S.Birthday, P.Logon = S.[logon]
--SELECT *
FROM OPENQUERY(STUDDATA97,'select * from Students') AS S INNER JOIN MBOA.CoreData.dbo.Persons AS P 
ON P.PersonID = CAST(S.StudentID AS nvarchar(20)) 
WHERE (S.LastName <> P.Lname OR S.FirstName <> P.Fname OR S.PreferedName <> P.Pname OR S.MiddleName <> P.Mname OR S.[Grad-Year] <> P.GradYear OR S.Citizenship <> P.Citizenship1 OR S.Citizenship2 <> P.Citizenship2 OR S.Nationality <> P.Nationality1 OR S.Sex <> P.Gender OR S.Birthday <> P.Bdate OR (P.Bdate IS NULL AND NOT S.Birthday IS NULL) OR S.Logon <> P.Logon OR (P.[Logon] IS NULL AND NOT S.Logon IS NULL));
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 02 New Students';
INSERT INTO MBOA.CoreData.dbo.Persons ( OldRVAID, OldIsStudent, Lname, Fname, Pname, Mname, Gradyear, Citizenship1, Citizenship2, Nationality1, Gender, Bdate, Logon, PersonID )
SELECT Students.StudentID, 1 AS Expr1, Students.LastName, Students.FirstName, Students.PreferedName, Students.MiddleName, Students.[Grad-Year], Students.Citizenship, Students.Citizenship2, Students.Nationality, Students.Sex, Students.Birthday, Students.logon, CAST(Students.StudentID AS nvarchar(20))
FROM OPENQUERY(STUDDATA97,'select * from students') Students LEFT JOIN MBOA.CoreData.dbo.Persons ON Persons.PersonID = CAST(Students.StudentID AS nvarchar(20)) 
WHERE Persons.PersonID IS NULL;
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 03 Existing Parents - No Bdate AcctNum';
UPDATE dbo_Persons
SET dbo_Persons.OldRVAID = [Parents].[ParentID], dbo_Persons.OldIsStudent = 0, 
	dbo_Persons.Fname = [FirstName], dbo_Persons.Mname = [MiddleName], 
	dbo_Persons.Lname = [LastName], dbo_Persons.MaidenName = [Parents].[MaidenName], 
	dbo_Persons.Pname = [PreferedName], dbo_Persons.Citizenship2 = [Parents].[Citizenship2], 
	dbo_Persons.Citizenship1 = [Citizenship], dbo_Persons.Nationality1 = [Nationality], 
	dbo_Persons.Title = [Parents].[Title], dbo_Persons.IsLiving = [Living], 
	dbo_Persons.Logon = [Login], dbo_Persons.Gender = [SEX]
-- SELECT * 
FROM OPENQUERY(STUDDATA97,'SELECT * FROM Parents') Parents INNER JOIN MBOA.CoreData.dbo.Persons AS dbo_Persons 
ON dbo_Persons.PersonID = CAST((Parents.[ParentID]+10000) AS nvarchar(20)) 
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]') [Staff Logins] ON Parents.ParentID = [Staff Logins].ParentID 
WHERE (dbo_Persons.Fname <> [FirstName] OR dbo_Persons.Mname <> [MiddleName] OR dbo_Persons.Lname <> [LastName] OR 
	   dbo_Persons.MaidenName <> [Parents].[MaidenName] OR  dbo_Persons.Pname <> [PreferedName] OR 
	   dbo_Persons.Citizenship2 <> [Parents].[Citizenship2] OR 
	   dbo_Persons.Citizenship1 <> [Citizenship] OR dbo_Persons.Nationality1 <> [Nationality] OR  
	   dbo_Persons.Title <> [Parents].[Title]  OR dbo_Persons.IsLiving <> [Living] OR  
	   (NOT [Login] IS NULL AND dbo_Persons.Logon <> [Login]) OR ((NOT [Sex] IS NULL AND [Sex] <> Gender) OR Gender IS NULL));
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 04 New Parents';
INSERT INTO MBOA.CoreData.dbo.Persons ( OldRVAID, OldIsStudent, Fname, Mname, Lname, MaidenName, Pname, Citizenship2, Citizenship1, Nationality1, Bdate, Account_Number, Title, IsLiving, PersonID, Logon,Gender )
SELECT Parents.ParentID, 0 AS Expr1, Parents.FirstName, Parents.MiddleName, Parents.LastName, 
	Parents.MaidenName, Parents.PreferedName, Parents.Citizenship2, Parents.Citizenship, 
	Parents.Nationality, Parents.Birthdate, [Account Number] AS Expr2, Parents.Title, [Living], 
	[Parents].[ParentID]+10000 AS Expr4, [Staff Logins].Login,SEX AS Gender
FROM (OPENQUERY(STUDDATA97,'SELECT * FROM Parents') Parents LEFT JOIN MBOA.CoreData.dbo.Persons dbo_Persons
ON dbo_Persons.PersonID = CAST((Parents.[ParentID]+10000) AS nvarchar(20))  
LEFT JOIN OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]') [Staff Logins] ON Parents.ParentID = [Staff Logins].ParentID)
WHERE dbo_Persons.PersonID IS NULL AND FirstName <> '' AND LastName <> '';
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 05 Delete Relationships';
DELETE FROM MBOA.CoreData.dbo.Person_Relationships;
-- ******************************************************************************************
PRINT 'Taylor - UPDATE MBOA - 06 - Add Relations - Father';
INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
SELECT P1,P2,R
FROM OPENQUERY(STUDDATA97,'SELECT [FatherID]+10000 AS P1, Students.StudentID AS P2, 1 AS R
FROM Students INNER JOIN Parents ON Parents.ParentID = Students.FatherID
WHERE (((Students.FatherID) Is Not Null And (Students.FatherID)<>4650)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''')
-- ******************************************************************************************
PRINT 'Taylor - UPDATE MBOA - 07 - Add Relations - Mother'
INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
SELECT P1,P2,R 
FROM OPENQUERY(STUDDATA97,'SELECT [MotherID]+10000 AS P1, Students.StudentID AS P2, 2 AS R
FROM Students  INNER JOIN Parents ON Parents.ParentID = Students.MotherID
WHERE (((Students.MotherID) Is Not Null)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''');
-- ******************************************************************************************
PRINT 'Taylor - UPDATE MBOA - 08 - Add Relations - PrimaryParent'
INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
SELECT P1,P2,R
FROM OPENQUERY(STUDDATA97,'SELECT [Students.ParentID]+10000 AS P1, Students.StudentID AS P2, 3 AS R
FROM Students  INNER JOIN Parents ON Parents.ParentID = Students.ParentID
WHERE (((Students.ParentID) Is Not Null)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''');
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 09 Delete All Email Usages'
DELETE FROM MBOA.CoreData.dbo.Email_Usage;
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 10 Delete Emails'
DELETE FROM MBOA.CoreData.dbo.Email_Addresses;
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 11 New Student Emails'
INSERT INTO MBOA.CoreData.dbo.Email_Addresses ( PersonID, Email )
SELECT CAST([StudentID] AS NVARCHAR(20)), [e-mail]
FROM OPENQUERY(STUDDATA97,'SELECT StudentID,[e-mail] FROM Students
WHERE NOT Students.[e-mail] Is Null');
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 12 New Parent Emails'
INSERT INTO MBOA.CoreData.dbo.Email_Addresses ( PersonID, Email )
SELECT CAST(([ParentID]+10000) AS NVARCHAR(20)), [e-mail]
FROM OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL')
WHERE CAST(([ParentID]+10000) AS NVARCHAR(20)) IN (SELECT PersonID FROM MBOA.CoreData.dbo.Persons)
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Priority'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Priority' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Priority] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Priority'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - General'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'General' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [General] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'General'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Guidance'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Guidance' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Guidance] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Guidance'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Business'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Business' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Business] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Business'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Furlough'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Furlough' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Furlough] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Furlough'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 5k'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, '5k' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [5k] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '5k'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 10k'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, '10k' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [10k] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '10k'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 50k'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, '50k' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [50k] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '50k'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Unlimited'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Unlimited' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Unlimited] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Unlimited'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Attachments'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Attachments' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Attachments] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Attachments'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - X-sensitive'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'X-sensitive' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [X-sensitive] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'X-sensitive'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Confidential'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Confidential' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Confidential] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Confidential'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Logon'
INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
select EA.emailID, 'Logon' as VAR
FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Logon] = -1') PE 
	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Logon'
WHERE Usage IS NULL
-- ******************************************************************************************
PRINT 'Add Current Staff Enrollment'
INSERT INTO MBOA.CoreData.dbo.Enrollment ( PersonID, Enrollment_Year, Enrollment_Term, Enrollment_Type, IsTeacher )
select [ParentID]+10000,School_Year,School_Term,'Staff',1
from openquery(studdata97,'Select * from parents') 
	INNER JOIN MBOA.CoreData.dbo.Current_YearTerm ON 1=1
 WHERE [Staff Active Now?] = 1 AND CAST([ParentID]+10000 AS varchar(20)) NOT IN (SELECT PersonID 
		FROM MBOA.CoreData.dbo.Enrollment WHERE School_Year = Enrollment_Year AND School_Term = Enrollment_Term)
-- ******************************************************************************************
PRINT 'Add Current Student Enrollment'
INSERT INTO MBOA.CoreData.dbo.Enrollment ( Enrollment_Year, Enrollment_Term, PersonID, Housing, Grade_in_School, Enrollment_Type, IsTeacher, SchoolID )
SELECT School_Year, Term, CAST([StudentID] AS Varchar(20)), ET.Housing, ET.[Grade in School], 'Student', 0, ET.SchoolID
FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]') ET
		INNER JOIN MBOA.CoreData.dbo.Current_YearTerm CYT ON ET.Year = OLDRVAYEAR
		WHERE CAST(StudentID AS Varchar(20)) NOT IN (SELECT PersonID 
		FROM MBOA.CoreData.dbo.Enrollment WHERE School_Year = Enrollment_Year AND Term = Enrollment_Term)
-- ******************************************************************************************
PRINT 'Update Current Student Enrollment'
-- Testing data:
--UPDATE MBOA.CoreData.dbo.Enrollment SET Grade_In_School = 11 
--	WHERE PersonID = '5597' AND Enrollment_Year = '2015-2016' AND Enrollment_Term = '1'
UPDATE MBOA.CoreData.dbo.Enrollment
SET E.Housing = ET.Housing, E.Grade_In_School = [Grade In School]
FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]') ET
		INNER JOIN MBOA.CoreData.dbo.Current_YearTerm CYT ON ET.Year = OLDRVAYEAR
		INNER JOIN MBOA.CoreData.dbo.Enrollment E ON CYT.School_Year = E.Enrollment_Year
		WHERE CAST(StudentID AS Varchar(20)) = PersonID AND ET.Term = E.Enrollment_Term
			AND (E.Housing <> ET.Housing OR E.Grade_In_School <> [Grade in School])
-- ******************************************************************************************
PRINT 'Delete Extra Enrollments'
-- Test Data
--INSERT INTO MBOA.CoreData.dbo.Enrollment (PersonID,Enrollment_Year,Enrollment_Term,Enrollment_Type,IsTeacher)
--VALUES(13424,'2015-2016',1,'Staff',1)
DELETE FROM MBOA.CoreData.dbo.Enrollment
-- SELECT * FROM MBOA.COREDATA.dbo.Enrollment
WHERE Enrollment_Type = 'Staff'
 AND Enrollment_Year IN (SELECT School_Year FROM MBOA.CoreData.dbo.Current_YearTerm )
	AND Enrollment_Term IN (SELECT School_Term FROM MBOA.CoreData.dbo.Current_YearTerm)
	AND PersonID NOT IN (
select [ParentID]+10000
from openquery(studdata97,'Select * from parents') 
 WHERE [Staff Active Now?] = 1)


DELETE FROM MBOA.CoreData.dbo.Enrollment 
WHERE  Enrollment_Type = 'Student' 
 AND Enrollment_Year IN (SELECT School_Year FROM MBOA.CoreData.dbo.Current_YearTerm )
 AND Enrollment_Term IN (SELECT School_Term FROM MBOA.CoreData.dbo.Current_YearTerm)
 AND PersonID NOT IN (SELECT CAST(StudentID AS Varchar(20)) 
					  FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]')
					  WHERE [YEAR] IN (SELECT OLDRVAYEAR 
					  FROM MBOA.CoreData.dbo.Current_YearTerm )
					  AND [Term] IN (SELECT School_Term 
					  FROM MBOA.CoreData.dbo.Current_YearTerm))
-- Insert Peris, Esther and Peter so they can do student lookups
INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
           ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
		   ,[IsTeacher],[Housing],[SchoolID])
     VALUES (99998,'2015-2016',2,'Staff',null,0,null,null);
INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
           ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
		   ,[IsTeacher],[Housing],[SchoolID])
     VALUES (99999,'2015-2016',2,'Staff',null,0,null,null);
INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
           ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
		   ,[IsTeacher],[Housing],[SchoolID])
     VALUES (99997,'2015-2016',2,'Staff',null,0,null,null);
INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
           ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
		   ,[IsTeacher],[Housing],[SchoolID])
     VALUES (99996,'2015-2016',2,'Staff',null,0,null,null);

-- Inserts the parent names and email info into powerschool, after removing the old records.
EXEC MBOA.CoreData.dbo.PS_SyncParentContacts 1
-- Synchronizes the name and login from the student database to powerschool
EXEC MBOA.CoreData.dbo.PS_SyncStudentContacts
-- Synchronizes the name, email and login from the student database to powerschool
EXEC MBOA.CoreData.dbo.PS_SyncTeacherContacts


END
